﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace web_api.Models
{
    public class DBhelper
    {
        //连接字符串
       static string constring = "server=.;database=Reticulum;Integrated Security=true;";
      //新增方法
        public static void Add(int UserID,string FileName,string newFileName,string kind, string savePath) 
        {

            SqlConnection con = new SqlConnection(constring);
            con.Open();
            int FileID = 1;
            string sql = $"insert into [File](UserID,newfileName,[FileName],FileID,address,kind) values({UserID}, '{newFileName}', '{FileName}', {FileID}, '{savePath}', '{kind}')";
            SqlCommand amd = new SqlCommand(sql, con);
            amd.ExecuteNonQuery();
            con.Close();
        }

        //验证登录
        public static int Login(string username, string password)
        {
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string sql = $"select*from [User] where username='{username}' and password='{password}'";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return 0;
            }
       
          
            return 3;

        }
        //显示文件方法
        public static List<File> Show(string sql,int allfile,List<File> files) 
        {
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                File file = new File();
                file.Filename = ds.Tables[0].Rows[i]["filename"].ToString();
                file.Fileaddress = ds.Tables[0].Rows[i]["address"].ToString();
                file.FileID = Convert.ToInt32(ds.Tables[0].Rows[i]["fileID"]);
                file.UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["UserID"]);
                file.Kind = ds.Tables[0].Rows[i]["kind"].ToString();
                files.Add(file);
            }
            return files;
        }

        //保存注册信息
        public static bool Upuser(string username,string password,string secret,string answer)
        {

            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string sql = $"insert into [User](username,password,secret,answer) values('{username}', '{password}', '{secret}', '{answer}')";
            SqlCommand amd = new SqlCommand(sql, con);
            if (amd.ExecuteNonQuery()>0)
            {
                con.Close();
                return true;
            }
            else
            {
                con.Close();
                return false;
            }
        }
        public static bool Retrieve(string username,string oldpassword,string newpassword,string sesecret,string answer)
        {
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string sql = $"update [User] set Password='{newpassword}' where Username='{username}' and Password='{oldpassword}' and Secret='{sesecret}' and Answer='{answer}'";
            SqlCommand amd = new SqlCommand(sql, con);
            if(amd.ExecuteNonQuery()>0)
            return true;
            else
            {
               
                return false;
            }
              
          
        }









    }
}